use crate::field::VddFieldRef;
use crate::i18n_text::E_ARG_BAD;
use crate::id_factory::ID_FACTORY;
use crate::model::{VddModel, VddSchema};
use crate::record::{RecordMeta, VddDisplay, VddRecord};
use crate::value::VddValue;
use crate::visit::VisitGroup;
use crate::FieldId;
use crate::{i18n, Ares, Astr, UserId, D_FORMAT};
use chrono::NaiveDate;
use serde::{Deserialize, Serialize};
use serde_json::Value;
use std::collections::HashMap;
use std::sync::Arc;

#[derive(Debug, Serialize)]
#[serde(rename_all = "camelCase")]
pub struct QueryDataPage<T> {
    pub data: Vec<Arc<T>>,
    pub all_record_count: usize,
    pub last_index: u64,
}

/// 查询结果
#[derive(Debug, Serialize)]
pub struct SqlQueryResult {
    pub fields: Vec<VddFieldRef>,
    pub records: Vec<Arc<VddDisplay>>,
    pub title: Astr,
}

impl SqlQueryResult {
    pub fn set_title(&mut self, title: &str) {
        self.title = title.into();
    }
}

#[derive(Debug)]
pub enum QueryFilter {
    QfCommiters(Vec<UserId>),                         // 数据贡献者
    QfUtsRange(Option<NaiveDate>, Option<NaiveDate>), // 最后更新时间范围
    QfFiledValue(Arc<VddValueEvaluation>),            // 字段过滤
}

/// web平台上的记录查询条件
#[derive(Debug)]
pub struct WebQueryParams {
    pub model_id: Option<u64>,
    pub page_index: u16,
    pub page_size: u16,
    pub last_id: u64,     // 客户端获取的最后一条记录的索引位置,用于可能的加速查询
    pub new_to_old: bool, // 是否从最新数据开始展示，默认为false
    pub filters: Vec<Arc<QueryFilter>>,
}

impl WebQueryParams {
    pub fn get_index_range(&self) -> (u64, u64) {
        let begin_index = (self.page_index as u64 - 1) * self.page_size as u64;
        let end_index = begin_index + self.page_size as u64;
        // tracing::info!("query index range is {} - {}",begin_index,end_index);
        (begin_index, end_index)
    }

    /// 判断一笔记录是否要加入返回结果中
    pub fn can_accept(&self, meta: &RecordMeta) -> Result<(), Astr> {
        for x in self.filters.iter() {
            let accept = match x.as_ref() {
                QueryFilter::QfCommiters(commiters) => {
                    if commiters.is_empty() {
                        true
                    } else {
                        commiters.contains(&meta.uid)
                    }
                }
                QueryFilter::QfUtsRange(begin, end) => match (begin, end) {
                    (Some(begin), Some(end)) => {
                        meta.update_ts.date().ge(&begin) && meta.update_ts.date().le(&end)
                    }
                    (Some(begin), None) => meta.update_ts.date().ge(&begin),
                    (None, Some(end)) => meta.update_ts.date().le(&end),
                    (None, None) => true,
                },
                _ => true,
            };
            // tracing::info!("accept {:?}:{:?}", x, accept);
            if !accept {
                return Err("".into());
            }
        }
        Ok(())
    }
}

/// 供前端展示用的树节点
#[derive(Debug, Serialize)]
#[serde(rename_all = "camelCase")]
pub struct WebTreeNode {
    pub key: u64,
    pub label: Astr,
    pub code: Astr,
    pub ext: Option<Astr>,
    pub children: Option<Vec<Arc<WebTreeNode>>>,
}

impl WebTreeNode {
    pub fn from_model_with_code(model: &VddModel, c: &str) -> Self {
        Self {
            key: model.id,
            label: model.name.clone(),
            code: Arc::from(c),
            ext: None,
            children: None,
        }
    }

    pub fn add_child(&mut self, key: u64, label: &str, code: &str, ext: Option<Astr>) {
        let mut childs = match self.children.clone() {
            Some(children) => children.clone(),
            None => Vec::<Arc<WebTreeNode>>::new(),
        };
        childs.push(Arc::new(Self {
            key,
            label: label.into(),
            code: code.into(),
            ext,
            children: None,
        }));
        self.children = Some(childs);
    }

    pub fn add_fields(&mut self, fields: &[VddFieldRef], tags: &HashMap<u64, Arc<str>>) {
        fields.iter().for_each(|f| {
            self.add_child(
                f.id.0,
                &f.label,
                &tags.get(&f.id.0).unwrap_or(&Arc::from("")),
                None,
            )
        });
    }
    pub fn add_sql_cols(&mut self, cols: &[Arc<str>]) {
        cols.iter()
            .for_each(|s| self.add_child(ID_FACTORY.lock().unwrap().lazy_generate(), s, s, None));
    }
}

impl QueryFilter {
    pub fn batch_from_json(j: &Value) -> Vec<Arc<Self>> {
        let mut r = Vec::<Arc<Self>>::new();
        if let Some(x) = j["drange"].as_array() {
            if x.len() == 2 {
                let b_date = x.get(0).unwrap().as_str().unwrap();
                let b_date = NaiveDate::parse_from_str(b_date, D_FORMAT).ok();
                let e_date = x.get(1).unwrap().as_str().unwrap();
                let e_date = NaiveDate::parse_from_str(e_date, D_FORMAT).ok();
                r.push(Arc::new(QueryFilter::QfUtsRange(b_date, e_date)));
            }
        }
        let committers = j["committers"].as_array().map(|x| {
            x.iter()
                .map(|y| y.as_u64())
                .filter(|y| y.is_some())
                .map(|y| UserId(y.unwrap()))
                .collect::<Vec<UserId>>()
        });
        if committers.is_some() {
            r.push(Arc::new(QueryFilter::QfCommiters(committers.unwrap())));
        }
        j["fvfs"].as_array().map(|x| {
            x.iter().for_each(|y| {
                let fve = VddValueEvaluation::try_from(y);
                if let Ok(fve) = fve {
                    r.push(Arc::new(QueryFilter::QfFiledValue(fve.into())));
                }
            });
        });
        r
    }
    /// 将查询参数内部的字段配置过滤条件抽取出来
    pub fn as_vdd_fve(&self) -> Option<Arc<VddValueEvaluation>> {
        match self {
            Self::QfCommiters(_) => None,
            Self::QfUtsRange(..) => None,
            Self::QfFiledValue(fve) => Some(fve.clone()),
        }
    }
}

impl TryFrom<&Value> for WebQueryParams {
    type Error = Astr;

    fn try_from(value: &Value) -> Result<Self, Self::Error> {
        let model_id = value["modelId"].as_u64();
        let page_index = value["pageIndex"].as_u64().unwrap_or(1) as u16;
        let page_size = value["pageSize"].as_u64().unwrap_or(100) as u16;
        let last_id = value["lastIndex"].as_u64().unwrap_or(0);
        let filters = QueryFilter::batch_from_json(&value["filters"]);
        let new_to_old = value["newToOld"].as_bool().unwrap_or(false);
        Ok(Self {
            model_id,
            page_index,
            page_size,
            last_id,
            filters,
            new_to_old,
        })
    }
}

/// 查询共享模型的记录时传递的参数
#[derive(Debug)]
pub struct ShareQueryParams {
    pub schema: Arc<VddSchema>,
    pub vg: Arc<VisitGroup>,
    pub params: Arc<WebQueryParams>,
}

impl ShareQueryParams {
    /// 判断一笔记录是否要加入返回结果中
    pub fn can_accept(&self, record: &mut VddRecord) -> Result<(bool, bool), Astr> {
        let r = self.params.can_accept(&record.meta);
        if r.is_err() {
            return Err(r.unwrap_err());
        }
        record.fit_vg(&self.vg, &self.schema.model.uid, &self.vg.u_id)
    }
}

#[derive(Debug, Serialize, Deserialize)]
#[serde(tag = "T", content = "C")]
pub enum VddValueEvaluation {
    FveEqual(FieldId, Astr),
    FvePartialMatch(FieldId, Astr),
    FveStartWith(FieldId, Astr),
    FveEndWith(FieldId, Astr),
    FveNotEqual(FieldId, Astr),
    FveNotContains(FieldId, Astr),
    FveBetween(FieldId, Astr, Astr),
    FveOutRange(FieldId, Astr, Astr),
    FveIn(FieldId, Vec<Astr>),
    FveExclude(FieldId, Vec<Astr>),
}

/// 匹配性比对
pub const C_FVE_EQ: &'static str = "FveEqual";
pub const C_FVE_NOT_EQ: &'static str = "FveNotEqual";
pub const C_FVE_PART_MATCH: &'static str = "FvePartialMatch";
pub const C_FVE_START_WITH: &'static str = "FveStartWith";
pub const C_FVE_END_WITH: &'static str = "FveEndWith";
pub const C_FVE_NOT_CONTAIN: &'static str = "FveNotContains";
/// 范围性比对
pub const C_FVE_BETWEEN: &'static str = "FveBetween";
pub const C_FVE_OUT_RANGE: &'static str = "FveOutRange";
/// 集合性比对
pub const C_FVE_IN: &'static str = "FveIn";
pub const C_FVE_NOT_IN: &'static str = "FveExclude";

impl TryFrom<&Value> for VddValueEvaluation {
    type Error = Astr;

    fn try_from(value: &Value) -> Result<Self, Self::Error> {
        let code = value["evaCode"].as_str().unwrap();
        let args = value["args"].as_array().map(|x| {
            x.iter()
                .map(|y| y.as_str())
                .filter(|y| y.is_some())
                .map(|y| y.unwrap())
                .map(|y| Arc::from(y))
                .collect::<Vec<Astr>>()
        });
        if args.is_none() {
            return Err("参数传值不完整".into());
        }
        let args = args.unwrap();
        if args.len() < 1 {
            return Err("参数传值不完整".into());
        }
        let fid = value["fid"].as_u64().unwrap();
        let fid = FieldId(fid);
        match code {
            C_FVE_EQ => {
                let p = args.first();
                if let Some(arg) = p {
                    Ok(Self::FveEqual(fid, arg.clone()).into())
                } else {
                    Err("参数传值不完整".into())
                }
            }
            C_FVE_NOT_EQ => {
                let p = args.first();
                if let Some(arg) = p {
                    Ok(Self::FveNotEqual(fid, arg.clone()).into())
                } else {
                    Err("参数传值不完整".into())
                }
            }
            C_FVE_PART_MATCH => {
                let p = args.first();
                if let Some(arg) = p {
                    Ok(Self::FvePartialMatch(fid, arg.clone()).into())
                } else {
                    Err("参数传值不完整".into())
                }
            }
            C_FVE_NOT_CONTAIN => {
                let p = args.first();
                if let Some(arg) = p {
                    Ok(Self::FveNotContains(fid, arg.clone()).into())
                } else {
                    Err("参数传值不完整".into())
                }
            }
            C_FVE_BETWEEN => {
                if args.len() != 2 {
                    Err("参数传值不合规".into())
                } else {
                    Ok(Self::FveBetween(
                        fid,
                        args.get(0).unwrap().clone(),
                        args.get(1).unwrap().clone(),
                    )
                    .into())
                }
            }
            C_FVE_OUT_RANGE => {
                if args.len() != 2 {
                    Err("参数传值不合规".into())
                } else {
                    Ok(Self::FveOutRange(
                        fid,
                        args.get(0).unwrap().clone(),
                        args.get(1).unwrap().clone(),
                    )
                    .into())
                }
            }
            C_FVE_IN => Ok(Self::FveIn(fid, args.clone()).into()),
            C_FVE_NOT_IN => Ok(Self::FveExclude(fid, args.clone()).into()),
            C_FVE_START_WITH => {
                let p = args.first();
                if let Some(arg) = p {
                    Ok(Self::FveStartWith(fid, arg.clone()).into())
                } else {
                    Err("参数传值不完整".into())
                }
            }
            C_FVE_END_WITH => {
                let p = args.first();
                if let Some(arg) = p {
                    Ok(Self::FveEndWith(fid, arg.clone()).into())
                } else {
                    Err("参数传值不完整".into())
                }
            }
            _ => Err(i18n!(E_ARG_BAD)),
        }
    }
}

impl VddValueEvaluation {
    /// 获取参与比对的字段ID
    pub fn get_field_id(&self) -> FieldId {
        match self {
            Self::FveEqual(fid, _) => fid.clone(),
            Self::FvePartialMatch(fid, _) => fid.clone(),
            Self::FveNotEqual(fid, _) => fid.clone(),
            Self::FveNotContains(fid, _) => fid.clone(),
            Self::FveBetween(fid, _, _) => fid.clone(),
            Self::FveOutRange(fid, _, _) => fid.clone(),
            Self::FveIn(fid, _) => fid.clone(),
            Self::FveExclude(fid, _) => fid.clone(),
            Self::FveStartWith(fid, _) => fid.clone(),
            Self::FveEndWith(fid, _) => fid.clone(),
        }
    }

    /// 评估一个列值是否符合查询条件
    pub fn evaluate(&self, fv: &VddValue) -> bool {
        match self {
            Self::FveEqual(_, v) => TryInto::<Astr>::try_into(fv)
                .map(|s| s == v.clone())
                .unwrap_or(false),
            Self::FvePartialMatch(_, v) => TryInto::<Astr>::try_into(fv)
                .map(|s| s.as_ref().contains(v.as_ref()))
                .unwrap_or(false),
            Self::FveStartWith(_, v) => TryInto::<Astr>::try_into(fv)
                .map(|s| s.as_ref().starts_with(v.as_ref()))
                .unwrap_or(false),
            Self::FveEndWith(_, v) => TryInto::<Astr>::try_into(fv)
                .map(|s| s.as_ref().ends_with(v.as_ref()))
                .unwrap_or(false),
            Self::FveNotEqual(_, v) => !TryInto::<Astr>::try_into(fv)
                .map(|s| s == v.clone())
                .unwrap_or(true),
            Self::FveNotContains(_, v) => !TryInto::<Astr>::try_into(fv)
                .map(|s| s.as_ref().contains(v.as_ref()))
                .unwrap_or(true),
            Self::FveBetween(_, v1, v2) => fv.is_between(v1.clone(), v2.clone()).unwrap_or(false),
            Self::FveOutRange(_, v1, v2) => !fv.is_between(v1.clone(), v2.clone()).unwrap_or(true),
            Self::FveIn(_, vs) => fv.is_in(vs).unwrap_or(false),
            Self::FveExclude(_, vs) => !fv.is_in(vs).unwrap_or(true),
        }
    }
    ///
    pub fn get_equal_compare_arg(&self) -> Option<Astr> {
        match self {
            Self::FveEqual(_, v) => Some(v.clone()),
            _ => None,
        }
    }
    /// 生成sql语句中的过滤条件部分, col_name表示列名称，
    pub fn to_mysql_where_part(&self, col_name: &str, value_need_quoter: bool) -> String {
        match self {
            Self::FveEqual(_, v) => {
                if value_need_quoter {
                    format!("{} = `{}`", col_name, v)
                } else {
                    format!("{} = {}", col_name, v)
                }
            }
            Self::FveNotEqual(_, v) => {
                if value_need_quoter {
                    format!("{} <> `{}`", col_name, v)
                } else {
                    format!("{} <> {}", col_name, v)
                }
            }
            VddValueEvaluation::FvePartialMatch(_, v) => {
                if value_need_quoter {
                    format!("{} LIKE `%{}%`", col_name, v)
                } else {
                    "".to_string()
                }
            }
            VddValueEvaluation::FveStartWith(_, v) => {
                if value_need_quoter {
                    format!("{} LIKE `{}%`", col_name, v)
                } else {
                    "".to_string()
                }
            }
            VddValueEvaluation::FveEndWith(_, v) => {
                if value_need_quoter {
                    format!("{} LIKE `%{}`", col_name, v)
                } else {
                    "".to_string()
                }
            }
            VddValueEvaluation::FveNotContains(_, v) => {
                if value_need_quoter {
                    format!("{} NOT LIKE `%{}%`", col_name, v)
                } else {
                    "".to_string()
                }
            }
            VddValueEvaluation::FveBetween(_, v1, v2) => {
                if value_need_quoter {
                    format!("{} BETWEEN `{}` AND `{}`", col_name, v1, v2)
                } else {
                    format!("{} BETWEEN {} AND {}", col_name, v1, v2)
                }
            }
            VddValueEvaluation::FveOutRange(_, v1, v2) => {
                if value_need_quoter {
                    format!("{} NOT BETWEEN `{}` AND `{}`", col_name, v1, v2)
                } else {
                    format!("{} NOT BETWEEN {} AND {}", col_name, v1, v2)
                }
            }
            VddValueEvaluation::FveIn(_, values) => {
                let mut s = String::new();
                values.iter().for_each(|v| {
                    if value_need_quoter {
                        s.push_str(&format!("`,{}`", v))
                    } else {
                        s.push_str(&format!(",{}", v))
                    }
                });
                if !s.is_empty() {
                    s.remove(0);
                }
                format!("{} IN ({})", col_name, s)
            }
            VddValueEvaluation::FveExclude(_, values) => {
                let mut s = String::new();
                values.iter().for_each(|v| {
                    if value_need_quoter {
                        s.push_str(&format!("`,{}`", v))
                    } else {
                        s.push_str(&format!(",{}", v))
                    }
                });
                if !s.is_empty() {
                    s.remove(0);
                }
                format!("{} NOT IN ({})", col_name, s)
            }
        }
    }
    /// 生成sql语句中的过滤条件部分, col_name表示列名称， join_by_or表示是否需要用or拼接，
    pub fn to_postgsql_where_part(&self, col_name: &str, value_need_quoter: bool) -> String {
        match self {
            Self::FveEqual(_, v) => {
                if value_need_quoter {
                    format!("{} = '{}'", col_name, v)
                } else {
                    format!("{} = {}", col_name, v)
                }
            }
            Self::FveNotEqual(_, v) => {
                if value_need_quoter {
                    format!("{} <> '{}'", col_name, v)
                } else {
                    format!("{} <> {}", col_name, v)
                }
            }
            VddValueEvaluation::FvePartialMatch(_, v) => {
                if value_need_quoter {
                    format!("{} LIKE '%{}%'", col_name, v)
                } else {
                    "".to_string()
                }
            }
            VddValueEvaluation::FveStartWith(_, v) => {
                if value_need_quoter {
                    format!("{} LIKE '{}%'", col_name, v)
                } else {
                    "".to_string()
                }
            }
            VddValueEvaluation::FveEndWith(_, v) => {
                if value_need_quoter {
                    format!("{} LIKE '%{}'", col_name, v)
                } else {
                    "".to_string()
                }
            }
            VddValueEvaluation::FveNotContains(_, v) => {
                if value_need_quoter {
                    format!("{} NOT LIKE '%{}%'", col_name, v)
                } else {
                    "".to_string()
                }
            }
            VddValueEvaluation::FveBetween(_, v1, v2) => {
                if value_need_quoter {
                    format!("{} BETWEEN '{}' AND '{}'", col_name, v1, v2)
                } else {
                    format!("{} BETWEEN {} AND {}", col_name, v1, v2)
                }
            }
            VddValueEvaluation::FveOutRange(_, v1, v2) => {
                if value_need_quoter {
                    format!("{} NOT BETWEEN '{}' AND '{}'", col_name, v1, v2)
                } else {
                    format!("{} NOT BETWEEN {} AND {}", col_name, v1, v2)
                }
            }
            VddValueEvaluation::FveIn(_, values) => {
                let mut s = String::new();
                values.iter().for_each(|v| {
                    if value_need_quoter {
                        s.push_str(&format!("',{}'", v))
                    } else {
                        s.push_str(&format!(",{}", v))
                    }
                });
                if !s.is_empty() {
                    s.remove(0);
                }
                format!("{} IN ({})", col_name, s)
            }
            VddValueEvaluation::FveExclude(_, values) => {
                let mut s = String::new();
                values.iter().for_each(|v| {
                    if value_need_quoter {
                        s.push_str(&format!("',{}'", v))
                    } else {
                        s.push_str(&format!(",{}", v))
                    }
                });
                if !s.is_empty() {
                    s.remove(0);
                }
                format!("{} NOT IN ({})", col_name, s)
            }
        }
    }
    /// 获取条件参数的可供展示内容
    pub fn get_params_display(&self) -> String {
        match self {
            Self::FveEqual(_, v)
            | Self::FveNotEqual(_, v)
            | Self::FvePartialMatch(_, v)
            | Self::FveStartWith(_, v)
            | Self::FveEndWith(_, v)
            | Self::FveNotContains(_, v) => {
                format!("{}", v)
            }
            Self::FveBetween(_, v1, v2) | Self::FveOutRange(_, v1, v2) => {
                format!("{}~{}", v1, v2)
            }
            Self::FveIn(_, values) | Self::FveExclude(_, values) => {
                let mut s = String::new();
                values.iter().for_each(|v| s.push_str(&format!(",{}", v)));
                if !s.is_empty() {
                    s.remove(0);
                }
                s
            }
        }
    }
}

/// 保存的查询计划
#[derive(Debug, Serialize, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct QueryPlan {
    pub id: u64,
    pub u_id: UserId, // 作者
    pub name: Astr,   // 计划名称
    pub sql: Astr,
}

impl QueryPlan {
    pub fn new(uid: &UserId, j: &Value) -> Self {
        Self {
            id: ID_FACTORY.lock().unwrap().lazy_generate(),
            u_id: uid.clone(),
            name: j["name"].as_str().unwrap_or("").into(),
            sql: j["sql"].as_str().unwrap_or("").into(),
        }
    }

    pub fn update_from_json(&mut self, j: &Value) {
        self.name = j["name"].as_str().unwrap_or("").into();
        self.sql = j["sql"].as_str().unwrap_or("").into();
    }

    pub fn validity_check(&self) -> Ares {
        Ok(())
    }
}
